﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Model;

namespace DAL
{
    public class ActivityDAL
    {
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public static int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("activity_id", "ExamDB.dbo.tb_Activity");
        }

         /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Activity GetModel(int activityid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from ExamDB.dbo.tb_Activity");
            strSql.Append(" where activity_id=@activityid");
            SqlParameter[] parameters = {
					new SqlParameter("@activityid", SqlDbType.Int,4)};
            parameters[0].Value = @activityid;
            Activity model = new Activity();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.Activity_id = int.Parse(ds.Tables[0].Rows[0]["activity_id"].ToString());
                model.Activity_enterpriseuid = int.Parse(ds.Tables[0].Rows[0]["activity_enterpriseuid"].ToString());
                model.Activity_positionid = int.Parse(ds.Tables[0].Rows[0]["activity_positionid"].ToString());
                model.Activity_name = ds.Tables[0].Rows[0]["activity_name"].ToString();
                model.Activity_content = ds.Tables[0].Rows[0]["activity_content"].ToString();
                model.Activity_status = int.Parse(ds.Tables[0].Rows[0]["activity_status"].ToString());
                model.Activity_logo = ds.Tables[0].Rows[0]["activity_logo"].ToString();
                model.Activity_viewreport = int.Parse(ds.Tables[0].Rows[0]["activity_viewreport"].ToString());
                model.Activity_welcometext = ds.Tables[0].Rows[0]["activity_welcometext"].ToString();
                model.Activity_companyintrotext = ds.Tables[0].Rows[0]["activity_companyintrotext"].ToString();

                if (ds.Tables[0].Rows[0]["activity_createtime"].ToString() != "")
                {
                    model.Activity_createtime = DateTime.Parse(ds.Tables[0].Rows[0]["activity_createtime"].ToString());
                }

                if (ds.Tables[0].Rows[0]["activity_holdtime"].ToString() != "")
                {
                    model.Activity_holdtime = DateTime.Parse(ds.Tables[0].Rows[0]["activity_holdtime"].ToString());
                }

                model.Activity_enterpriseuid = int.Parse(ds.Tables[0].Rows[0]["activity_enterpriseuid"].ToString());
                model.Activity_positionid = int.Parse(ds.Tables[0].Rows[0]["activity_positionid"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string eUserID)
        {
            if (string.IsNullOrEmpty(eUserID))
            {
                return null;
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT 
                        ACTIVITY_ID,
                        ACTIVITY_ENTERPRISEUID,
                        ACTIVITY_POSITIONID,
                        ACTIVITY_NAME,
                        POSITION_ID,
                        POSITION_NAME,
                        (select count(*) from EXAMDB.DBO.tb_GUID_Number where tb_GUID_Number.activity_id=TB_ACTIVITY.ACTIVITY_ID) as totalGUID,
                        (select count(*) from EXAMDB.DBO.tb_GUID_Number where tb_GUID_Number.activity_id=TB_ACTIVITY.ACTIVITY_ID and guid_status <>0) as usedGUID,
                        (select count(*) from EXAMDB.DBO.tb_GUID_Number where tb_GUID_Number.activity_id=TB_ACTIVITY.ACTIVITY_ID and guid_status =0) AS remainGUID,
                        (select sum(payment_money) from EXAMDB.DBO.tb_Activity_Payment where tb_Activity_Payment.activity_id=TB_ACTIVITY.ACTIVITY_ID ) activity_money,                  
                        activity_createtime,
                        activity_holdtime,
                        CASE activity_viewreport 
                        WHEN 1111111 THEN '允许' 
                        WHEN 1111121 THEN '允许'
                        ELSE '不允许' END AS activity_viewreport,
                        CASE ACTIVITY_STATUS WHEN 0 THEN '开启项目' ELSE '停止项目' END AS ACTIVITY_STATUS 
                        FROM EXAMDB.DBO.TB_ACTIVITY,EXAMDB.DBO.TB_POSITION 
                        WHERE EXAMDB.DBO.TB_ACTIVITY.ACTIVITY_POSITIONID=EXAMDB.DBO.TB_POSITION.POSITION_ID ");
            
            strSql.Append(" AND ACTIVITY_ENTERPRISEUID = " + eUserID);            

            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DataSet GetActivityByPositionId(string positionId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from TB_ACTIVITY where activity_positionid='");
            sb.Append(positionId).Append("'");
            DataSet ds = DbHelperSQL.Query(sb.ToString());
            return ds;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetListAndDimen(string eUserID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT 
                        ACTIVITY_ID,
                        ACTIVITY_ENTERPRISEUID,
                        ACTIVITY_POSITIONID,
                        ACTIVITY_NAME,
                        POSITION_ID,
                        POSITION_NAME,
(select count(*) from EXAMDB.DBO.tb_GUID_Number where tb_GUID_Number.activity_id=TB_ACTIVITY.ACTIVITY_ID) as totalGUID,
(select count(*) from EXAMDB.DBO.tb_GUID_Number where tb_GUID_Number.activity_id=TB_ACTIVITY.ACTIVITY_ID and guid_status <>0) as usedGUID,
(select count(*) from EXAMDB.DBO.tb_GUID_Number where tb_GUID_Number.activity_id=TB_ACTIVITY.ACTIVITY_ID and guid_status =0) AS remainGUID,
                        (select sum(payment_money) from EXAMDB.DBO.tb_Activity_Payment where tb_Activity_Payment.activity_id=TB_ACTIVITY.ACTIVITY_ID ) activity_money,                  
                        activity_createtime,
                        activity_holdtime,

                        (select count(*)  from ExamDB.dbo.tb_Activity_Dimension where ExamDB.dbo.tb_Activity_Dimension.ACTIVITY_ID= ExamDB.dbo.tb_Activity.ACTIVITY_ID )as dimension_num,
                        CASE activity_viewreport WHEN 0 THEN '允许' ELSE '不允许' END AS activity_viewreport,
                        CASE ACTIVITY_STATUS WHEN 0 THEN '停用' ELSE '启用' END AS ACTIVITY_STATUS 
                        FROM EXAMDB.DBO.TB_ACTIVITY,EXAMDB.DBO.TB_POSITION 
                        WHERE EXAMDB.DBO.TB_ACTIVITY.ACTIVITY_POSITIONID=EXAMDB.DBO.TB_POSITION.POSITION_ID");

            if (!string.IsNullOrEmpty(eUserID))
            {
                strSql.Append(" AND ACTIVITY_ENTERPRISEUID = " + eUserID);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="activity_id"></param>
        public static void Delete(int activity_id)
        { 
            string a_id = activity_id.ToString();
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append(" delete tb_Activity where activity_id=").Append(a_id);
            strSql.Append(" delete tb_Activity_Dimension where activity_id=").Append(a_id);
            strSql.Append(" delete tb_Activity_Payment where activity_id=").Append(a_id);
            strSql.Append(" delete tb_GUID_Number where activity_id=").Append(a_id);

            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Activity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ExamDB.dbo.tb_Activity(");
            strSql.Append(@"activity_enterpriseuid,
                            activity_positionid,
                            activity_name,
                            activity_content,
                            activity_status,
                            activity_logo,
                            activity_viewreport,
                            activity_welcometext,
                            activity_createtime,
                            activity_holdtime,
                            activity_companyintrotext)");

            strSql.Append(" values (");

            strSql.Append(@"@activity_enterpriseuid,
                            @activity_positionid,
                            @activity_name,
                            @activity_content,
                            @activity_status,
                            @activity_logo,
                            @activity_viewreport,
                            @activity_welcometext,
                            @activity_createtime,
                            @activity_holdtime,
                            @activity_companyintrotext)");

            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@activity_enterpriseuid", SqlDbType.Int,4),
					new SqlParameter("@activity_positionid", SqlDbType.Int,4),
					new SqlParameter("@activity_name", SqlDbType.NVarChar,255),
					new SqlParameter("@activity_content", SqlDbType.NVarChar,255),
                    new SqlParameter("@activity_status", SqlDbType.Int ,4),
                    new SqlParameter("@activity_logo", SqlDbType.NVarChar,255),
                    new SqlParameter("@activity_viewreport", SqlDbType.Int ,4),
                    new SqlParameter("@activity_welcometext", SqlDbType.NVarChar,255),
                    new SqlParameter("@activity_createtime",SqlDbType.DateTime),
                    new SqlParameter("@activity_holdtime",SqlDbType.DateTime),
                    new SqlParameter("@activity_companyintrotext", SqlDbType.NVarChar,255)
                                        };
            parameters[0].Value = model.Activity_enterpriseuid;
            parameters[1].Value = model.Activity_positionid;
            parameters[2].Value = model.Activity_name;
            parameters[3].Value = model.Activity_content;
            parameters[4].Value = model.Activity_status;
            parameters[5].Value = model.Activity_logo;
            parameters[6].Value = model.Activity_viewreport;
            parameters[7].Value = model.Activity_welcometext;
            parameters[8].Value = model.Activity_createtime;
            parameters[9].Value = DBNull.Value;
            parameters[10].Value =  model.Activity_companyintrotext;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static int Update(Activity  model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update ExamDB.dbo.tb_Activity set ");
            strSql.Append("activity_content=@activity_content,");
            strSql.Append("activity_welcometext=@activity_welcometext,");
            strSql.Append("activity_companyintrotext=@activity_companyintrotext,");
            strSql.Append("activity_logo=@activity_logo");
            strSql.Append(" where activity_id=@activity_id");

            SqlParameter[] parameters = {
                    new SqlParameter("@activity_content", SqlDbType.NVarChar,255),                                
				    new SqlParameter("@activity_welcometext", SqlDbType.NVarChar,255),
					new SqlParameter("@activity_companyintrotext", SqlDbType.NVarChar,255),
					new SqlParameter("@activity_logo", SqlDbType.NVarChar,255),
					new SqlParameter("@activity_id", SqlDbType.Int,4)};

            parameters[0].Value = model.Activity_content;
            parameters[1].Value = model.Activity_welcometext;
            parameters[2].Value = model.Activity_companyintrotext;
            parameters[3].Value = model.Activity_logo;
            parameters[4].Value = model.Activity_id;

            return  DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public static int GetPositionId(int activityId)
        {
            int ret =-1;
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select activity_positionid from dbo.tb_Activity where activity_id = ");
            strSql.Append("'");
            strSql.Append(activityId.ToString());
            strSql.Append("'");

            try
            {
                DataSet GUIDds = DbHelperSQL.Query(strSql.ToString());
                if (GUIDds != null && GUIDds.Tables[0].Rows.Count == 1)
                {
                    ret = Int32.Parse(GUIDds.Tables[0].Rows[0][0].ToString());
                }
                return ret;
            }
            catch
            {
                ret = -1;
                return ret;
            }
        }

        public static int UpdateActivityRptSettings(string actid,int RPTvalue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TB_ACTIVITY SET ACTIVITY_VIEWREPORT = ").Append(RPTvalue.ToString());
            strSql.Append(" WHERE ACTIVITY_ID = ").Append(actid.ToString());
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        public static int UpdateActivityStatus(string actid, string ponsitionid, string enterpriseid, bool isDisable)
        {
            StringBuilder strSql = new StringBuilder();
            if (true == isDisable)
            {
                strSql.Append("update ExamDB.dbo.tb_Activity set activity_status ");
                strSql.Append("=0,activity_holdtime='" + DateTime.Now.ToString());
                strSql.Append("' where activity_id='");
                strSql.Append(actid + "' and activity_enterpriseuid=" + enterpriseid +
                    " and activity_positionid=" + ponsitionid);
            }
            else
            {
                strSql.Append("update ExamDB.dbo.tb_Activity set activity_status ");
                strSql.Append("=1,activity_holdtime=null");
                strSql.Append(" where activity_id='");
                strSql.Append(actid + "' and activity_enterpriseuid=" + enterpriseid +
                    " and activity_positionid=" + ponsitionid);
            }
            if (DbHelperSQL.Query(strSql.ToString()) != null)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }

        public static string GetActivityNM(int activity_id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select activity_name from dbo.tb_Activity where activity_id = ");
            strSql.Append("'");
            strSql.Append(activity_id.ToString());
            strSql.Append("'");
            try
            {
                DataSet ds = DbHelperSQL.Query(strSql.ToString());
                if (ds != null && ds.Tables[0].Rows.Count == 1)
                {
                    return ds.Tables[0].Rows[0][0].ToString();
                }

                return string.Empty;
            }
            catch
            {
                return string.Empty; 
            }
        }

        public static  DataSet GetEnterpriseInfoByactivityId(int activity_id)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select activity_enterpriseuid,users_incname 
                            from tb_Activity,tb_EnterpriseUsers 
                            where tb_Activity.activity_enterpriseuid=tb_EnterpriseUsers.users_id 
                            and activity_id = ");
            strSql.Append("'");
            strSql.Append(activity_id.ToString());
            strSql.Append("'");

            try
            {
                DataSet ds = DbHelperSQL.Query(strSql.ToString());
                if (ds != null && ds.Tables[0].Rows.Count == 1)
                {
                    return ds;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }
    }
}